﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<全部工作日志列表IN>
-- =============================================
CREATE PROCEDURE [dbo].[proc_WorkLog_ALL_GetListIn]
(
	@LogType INT,
	@StartIndex int,
	@EndIndex INT,
	@inCompanyId NVARCHAR(500),
	@Stext NVARCHAR(200),
	@StartDate NVARCHAR(10),
	@EndDate NVARCHAR(10)
)
AS
BEGIN
	DECLARE @SqlStr NVARCHAR(3000)
	DECLARE @cSql NVARCHAR(300)

	If @LogType = 1
		Set @cSql = ' And (LogType In(1,3)'
	Else
		Set @cSql = ' And (LogType=' + Convert(varchar(10),@LogType)

	If @Stext<>''
		Set @cSql =@cSql+ ' And (Title like ''%'+@Stext+'%'' Or Name like ''%'+@Stext+'%'')'
	
	SET @SqlStr='
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

	;WITH List As(Select ROW_NUMBER() OVER (Order By WorkLog.CreateTime Desc) AS Row,
		id,
		GID, 
		Title, 
		[Content], 
		CreateTime, 
		EditTime, 
		UserName, 
		Name, 
		CompanyId,
		ISNULL((SELECT Company.CompanyName FROM Company WHERE Company.Id=worklog.CompanyId),'''') AS CompanyName,
		DepId, 
		ISNULL((SELECT Department.DName  FROM Department WHERE Department.Id=WorkLog.DepId),'''') AS DepName,
		LogType
	    FROM WorkLog
	    WHERE CompanyId IN ('+@inCompanyId+')
	    And CreateTime>=@sRq And CreateTime<=@eRq
	    '+@cSql+'	
	)

	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' and '+Convert(varchar(10),@EndIndex)+' Order By Row'
	EXEC(@SqlStr)
END
